﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using ScoreHelper.Properties;
using System.Drawing;
namespace ScoreHelper
{
    
    public class ScoreManager
    {
        public struct ScoreOption
        {
            public bool Checked; // 是否选中
            public int Column; // 数据对应列
            public string Element; // checkbox元素名
            public string Name; // 名字
            public string ScoreElem; // 对应分数列
            
        }

        public struct Course
        {
            public string Name;
            public string Number;
            public double[] ScoreRatio; // 系数,名字见ScoreOption
        }

        public delegate void ItemFilled(int CurrentIndex, int TotalCount, int FilledCount);
        public delegate bool FillFinished(int TotalCount, int FilledCount);
        private ScoreOption[] scoreOption;
        private Course[] courses;
        private int startRow = 1, startCol = 1;
        public ItemFilled OneItemFilled;
        public FillFinished OnFinish;
        public ScoreManager()
        {
            string[] st = Settings.Default.ScoreType.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
            string[] et = Settings.Default.ScoreChkElems.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
            string[] se = Settings.Default.ScoreElems.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
            if (st.Length < 1) 
                return;
            scoreOption = new ScoreOption[st.Length];
            for (int i = 0; i < scoreOption.Length; i++)
            {
                scoreOption[i].Checked = false;
                scoreOption[i].Name = st[i];
                scoreOption[i].Element = et[i];
                scoreOption[i].ScoreElem = se[i];
                scoreOption[i].Column = 0;
                
            }
        }


        public void RefreshOption(System.Windows.Forms.HtmlElement form)
        {
            try
            {
                for (int i = 0; i < scoreOption.Length; i++)
                {
                    scoreOption[i].Checked = (form.All[scoreOption[i].Element].GetAttribute("checked").ToLower().Equals("true"));
                }
            }
            catch(Exception ex)
            {
                throw ex;
            }
        }



        // 检查成绩单格式
        public bool CheckFormat(string strFile)
        {

            bool bOK = false;
            Application xlsApp = null;
            Workbook wb = null;
            Range cellXH = null, cellXM = null, cellTemp, rng = null;
            try
            {
                xlsApp = new Application();

                wb = xlsApp.Workbooks.Open(strFile, Type.Missing, true, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing);
                foreach (Worksheet ws in wb.Worksheets)
                {
                    rng = (Range)ws.Cells;
                    cellXH = rng.Find(Settings.Default.StudentNumber, Type.Missing, XlFindLookIn.xlValues,
                    XlLookAt.xlWhole, XlOrder.xlOverThenDown, XlSearchDirection.xlNext, false, false, false);
                    cellXM = rng.Find(Settings.Default.StudentName, Type.Missing, XlFindLookIn.xlValues,
                        XlLookAt.xlWhole, XlOrder.xlOverThenDown, XlSearchDirection.xlNext, false, false, false);
                    if (cellXH != null && cellXM != null)
                        break;
                }
//                 rng = ((Worksheet)wb.Worksheets[1]).Cells;
//                 cellXH = rng.Find(Settings.Default.StudentNumber, Type.Missing, XlFindLookIn.xlValues,
//                     XlLookAt.xlWhole, XlOrder.xlOverThenDown, XlSearchDirection.xlNext, false, false, false);
//                 cellXM = rng.Find(Settings.Default.StudentName, Type.Missing, XlFindLookIn.xlValues,
//                     XlLookAt.xlWhole, XlOrder.xlOverThenDown, XlSearchDirection.xlNext, false, false, false);
//                 bOK = (cellXH != null && cellXM != null);

                if (cellXH == null)
                    throw new Exception("缺少学号列，请点击查看成绩单模板文件。");
                if (cellXM == null)
                    throw new Exception("缺少姓名列，请点击查看成绩单模板文件。");

                if( cellXH.Row != cellXM.Row  || (cellXM.Column - cellXH.Column != 1))
                    throw new Exception("姓名和学号必须一一对应，且学号在姓名前一列，请查看成绩单模板文件。");
                startRow = cellXH.Row;
                startCol = cellXH.Column;

                string missCol = "";
                bOK = false;
                for (int i = 0; i < scoreOption.Length; i++)
                {
                    if (!scoreOption[i].Checked)
                        continue;
                    bOK = true;
                    cellTemp = rng.Find(scoreOption[i].Name, Type.Missing, XlFindLookIn.xlValues,
                        XlLookAt.xlWhole, XlOrder.xlOverThenDown, XlSearchDirection.xlNext, false, false, false);
                    if (cellTemp == null)
                        missCol += " " + scoreOption[i].Name;
                    else
                        scoreOption[i].Column = cellTemp.Column;
                }
                if(!bOK)
                    throw new Exception("没有指定任何成绩选项，至少选择一项 期末成绩。");

                if (missCol.Length > 3)
                    throw new Exception("缺少列：" + missCol + "，请检查成绩单和成绩选项。");
                
                // 检查数据行数和学号一列的格式
                if(cellXH.EntireColumn.End[XlDirection.xlDown].Row < 2)
                    throw new Exception("没有包含成绩数据。");
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                cellXM = null;
                cellXH = null;
                cellTemp = null;
                rng = null;
                wb = null;
                if (xlsApp != null)
                {
                    xlsApp.Quit();
                    xlsApp = null;
                }
                
            }
            return bOK;
            
        }
        
        // 显示模板文件
        //TODO: 添加图表
        public void ShowTemplate(System.Windows.Forms.HtmlElement form)
        {

            Application xlsApp = null;
            Workbook wb = null;
            Range rng = null;
            string sheetName = "Sheet1";
            List<string> scoreItems = new List<string>(scoreOption.Length);
            int studentCount = 0;
            try
            {
                int j = 3, i = 0; // 成绩项数
                for (i = 0; i < scoreOption.Length; i++)
                {
                    if (scoreOption[i].Checked)
                        scoreItems.Add(scoreOption[i].Name);
                }
                scoreItems.Add(Settings.Default.FinalScore); // 最终（总）成绩
                if (scoreItems.Count < 2)
                    throw new Exception("至少选择一种成绩。");

                xlsApp = new Application();

                wb = xlsApp.Workbooks.Add(Type.Missing);
                rng = ((Worksheet)wb.Worksheets[1]).Cells;
                ((Worksheet)wb.Worksheets[1]).Name = "成绩单";
                sheetName = ((Worksheet)wb.Worksheets[1]).Name;
                rng[1, 1] = Settings.Default.StudentNumber; // 学号标题
                ((Range)rng[1, 1]).ColumnWidth = 15;
                rng[1, 2] = Settings.Default.StudentName; // 姓名标题
                ((Range)rng[1, 2]).ColumnWidth = 8;
                //成绩项
                for (i = 0; i < scoreItems.Count; i++)
                {
                    rng[1, 3 + i] = scoreItems[i];
                }
               
                // 填充名单
                if (form != null)
                {
                    System.Windows.Forms.HtmlElement Tbl = null;
                    System.Windows.Forms.HtmlElementCollection Tables = form.GetElementsByTagName("TABLE");
                    if (Tables.Count > 1)
                        Tbl = Tables[1];
                    if (Tbl != null)
                    {
                        System.Windows.Forms.HtmlElementCollection rows = Tbl.GetElementsByTagName("TR");
                        for (i = 1, studentCount = 0; i < rows.Count; i++, studentCount++)
                        {
                            System.Windows.Forms.HtmlElementCollection cols = rows[i].GetElementsByTagName("TD");
                            rng[i + 1, 1] = cols[2].InnerText.Trim();// 学号
                            rng[i + 1, 2] = cols[3].InnerText.Trim(); // 姓名
                        }
                        Tbl = null;
                    }
                }
                
                // 调整格式
                ((Range)rng[1, 1]).EntireRow.Font.Bold = true;
                ((Range)rng[1, 1]).EntireRow.HorizontalAlignment = XlHAlign.xlHAlignCenter;
                ((Range)rng[1, 1]).EntireColumn.NumberFormatLocal = "0";


                // 取成绩系数?填写总成绩公式
                if (studentCount < 1)
                    studentCount = 1;
                Range rngFinalScore = ((Range)rng).get_Range(rng[2, 2 + scoreItems.Count], rng[1 + studentCount, 2 + scoreItems.Count]);
                
                if (scoreItems.Count > 2)
                    rngFinalScore.FormulaR1C1 = "=R[0]C[-2]*0.3+R[0]C[-1]*0.7";
                else if(scoreItems.Count == 2)
                    rngFinalScore.FormulaR1C1 = "=R[0]C[-1]";

                // 插入统计数字和图表
                ((Worksheet)wb.Worksheets[2]).Name = "统计图表";
                rng = ((Worksheet)wb.Worksheets[2]).Cells;
                rng[2, 2] = "成绩分布";
                rng[2, 9] = "统计指标";
                ((Range)rng[2, 2]).Font.Bold = true;
                ((Range)rng[2, 9]).Font.Bold = true;
                j = 3;
                for (i = 0; i < scoreItems.Count; i++)
                {
                    rng[2, i+3] = scoreItems[i];
                    rng[2, i+10] = scoreItems[i];
                }

                string[] scoreSeg = Settings.Default.ScoreSeg.Split(new char[]{'|'}, StringSplitOptions.RemoveEmptyEntries);
                string[] scoreSegF = Settings.Default.ScoreSegFormula.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                string[][] scoreSegFormula = new string[scoreSegF.Length][];
                string fTemplate = "=COUNTIFS({0}!R2C{1}:R1001C{1},\"{2}\",{0}!R2C{1}:R1001C{1},\"{3}\")";
                for (i = 0; i < scoreSeg.Length; i++ )
                {
                    scoreSegFormula[i] = scoreSegF[i].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
                    rng[3 + i, 2] = scoreSeg[i];
                }
                
                for (i = 0; i < scoreSeg.Length; i++)
                {
                    for (j = 0; j < scoreItems.Count; j++)
                    {
                        ((Range)rng[i + 3, j + 3]).FormulaR1C1 = string.Format(fTemplate, sheetName, j+3, scoreSegFormula[i][0], scoreSegFormula[i][1]);
                    }
                }

                string[] statItem = Settings.Default.StatItem.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                string[] statFunc = Settings.Default.StatFunc.Split(new char[] { '|' }, StringSplitOptions.RemoveEmptyEntries);
                string gTemplate = "={1}({0}!R2C{2}:R1000C{2})";
                for (i = 0; i < statItem.Length; i++)
                {
                    rng[3 + i, 9] = statItem[i];
                }
                for (i = 0; i < statItem.Length; i++)
                {
                    for (j = 0; j < scoreItems.Count; j++)
                    {
                        ((Range)rng[i + 3, j + 10]).FormulaR1C1 = string.Format(gTemplate,  sheetName, statFunc[i], j + 3);
                    }
                }

                // 图表
                Range dataRng = ((Range)rng).get_Range(rng[2,2], rng[2 + scoreSeg.Length, 2 + scoreItems.Count]);
                Shape shape1 = ((Worksheet)wb.Worksheets[2]).Shapes.AddChart(XlChartType.xlColumnClustered, 20, 150, 360, 240);
                shape1.Chart.PlotBy = XlRowCol.xlColumns;
                shape1.Chart.SetSourceData(dataRng);
                shape1.Chart.Legend.Position = XlLegendPosition.xlLegendPositionTop;

                Shape shape2= ((Worksheet)wb.Worksheets[2]).Shapes.AddChart(XlChartType.xlColumnStacked100, 400, 150, 360, 240);
                shape2.Chart.SetSourceData(dataRng);
                shape2.Chart.PlotBy = XlRowCol.xlRows;
                shape2.Chart.Legend.Position = XlLegendPosition.xlLegendPositionTop;

                xlsApp.Visible = true;
            }
            catch (Exception ex)
            {
                if(xlsApp != null)
                    xlsApp.Quit();
                throw ex;
            }
            finally
            {
                rng = null;
                wb = null;
                
            }
        }

        // 填充
        public void Fill(string strFile, System.Windows.Forms.HtmlElement form)
        {
            if (form == null)
                return;
            int i = 0, j = 0, index = 0;
            string strXH = "";
            Application xlsApp = null;
            Workbook wb = null;
            Range rng, rngXH, cell = null;

            int TotalCount = 0, FilledCount = 0;
            try
            {
                System.Windows.Forms.HtmlElementCollection[] elemsScores = new System.Windows.Forms.HtmlElementCollection[scoreOption.Length];
                for (i = 0; i < scoreOption.Length; i++)
                {
                    if (scoreOption[i].Checked)
                        elemsScores[i] = form.All.GetElementsByName(scoreOption[i].ScoreElem);
                        
                    else
                        elemsScores[i] = null;
                }
                System.Windows.Forms.HtmlElementCollection elemsXH = form.All.GetElementsByName(Settings.Default.StudentNumElem);

                TotalCount = elemsXH.Count;

                xlsApp = new Application();
                xlsApp.DisplayAlerts = false;
                xlsApp.ScreenUpdating = false;

                wb = xlsApp.Workbooks.Open(strFile, Type.Missing, false, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                    Type.Missing, Type.Missing, Type.Missing);
                rng = ((Worksheet)wb.Worksheets[1]).Cells;
                
                rng[1, 9] = Settings.Default.SerialNum;
                // 突出没有填充的报告
                rng[1, 11] = Settings.Default.NotFountStu;
                //((Range)(rng[1, 11])).EntireColumn.Interior.Color = 65535;
                ((Range)(rng[1, 11])).EntireColumn.Font.Color = Color.Red;
                // 先把学号全部显示，再只在学号一列查找
                //rngXH = rng.Find(Settings.Default.StudentNumber, Type.Missing, XlFindLookIn.xlValues,
                //    XlLookAt.xlWhole, XlOrder.xlOverThenDown, XlSearchDirection.xlNext, false, false, false);
                //rngXH = rngXH.EntireColumn;
                rngXH = ((Range)rng[startRow, startCol]).EntireColumn; // 只在学号一列查找
                rngXH.NumberFormatLocal = "0"; // 设置数字格式
                for (i = 0; i < TotalCount; i++)
                {
                    index = i + 1;
                    strXH = elemsXH[i].GetAttribute("value").Trim();
                    cell = rngXH.Find(strXH, (cell == null ? Type.Missing : cell), XlFindLookIn.xlValues,
                        XlLookAt.xlWhole, XlOrder.xlDownThenOver, XlSearchDirection.xlNext, false, false, false);
                    if (cell == null) // Excel中没找到
                    {
                        // 把学号和编号存储到报告
                        rng[index - FilledCount + 1, 11] = index + "-" + strXH;
                        
                        if (OneItemFilled != null)
                            OneItemFilled(index, TotalCount, FilledCount);
                        continue;
                    }
                    else // 找到了
                    {
                        // 填充选中的几项成绩
                        for (j = 0; j < scoreOption.Length; j++)
                        {
                            if (elemsScores[j] != null && scoreOption[j].Checked && scoreOption[j].Column > 0)
                            {
                                elemsScores[j][i].SetAttribute("value", ((Range)rng[cell.Row, scoreOption[j].Column]).Text.ToString());
                            }
                        }
                        // 记录填充序号
                        rng[cell.Row, 9] = index;
                        FilledCount++;
                    }

                    if(OneItemFilled != null)
                        OneItemFilled(i+1, TotalCount, FilledCount);
                }
                wb.Save();
                xlsApp.DisplayAlerts = true;
                xlsApp.ScreenUpdating = true;
                if (OnFinish != null)
                {
                    if (OnFinish(TotalCount, FilledCount))
                    {
                        xlsApp.Visible = true;
                    }
                    else
                    {
                        xlsApp.Quit();
                    }
                    xlsApp = null;
                }
                
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                rng = null;
                if (xlsApp != null)
                {
                    if (wb != null)
                    {
                        wb = null;
                    }
                    xlsApp.Quit();
                    xlsApp = null;
                }
                
            }
        }
        
    }
}
